package com.foodlocker.sqlite;

import java.util.Date;

import com.foodlocker.bean.Courier;
import com.foodlocker.bean.Order;
import com.foodlocker.common.Constant;
import com.foodlocker.common.DateTimeFunction;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.widget.Toast;



public class OrderDao {


	

	private DBHelper dbhelper;
	private SQLiteDatabase db;
	Context context;
	String table="tb_order";
	public OrderDao(Context context) {
		dbhelper= new DBHelper(context);
		db=dbhelper.getWritableDatabase();
		this.context=context; 
	}
	
	public JSONArray insert(JSONArray orderJA){
		
		           System.out.println("insert---------"+orderJA);
		   
		        	 for(int i=orderJA.size()-1;i>=0;i--){
		        		  try{ 
		        	 JSONObject orderJO=orderJA.getJSONObject(i);
			           ContentValues cv = new ContentValues();			   
			           cv.put(Order.ID, orderJO.getLong(Order.ID));          
			           cv.put(Order.CUSTOMERTELNUM, orderJO.getString(Order.CUSTOMERTELNUM));
			           cv.put(Order.SUPPLIERTELNUM, orderJO.getString(Order.SUPPLIERTELNUM));
			           cv.put(Order.SERVICETELNUM, orderJO.getString(Order.SERVICETELNUM));
			           cv.put(Order.SECRET ,orderJO.getString(Order.SECRET));	
			           cv.put(Order.BOXESIN, orderJO.getString(Order.BOXESIN));	
			           cv.put(Order.STATE, orderJO.getInt(Order.STATE));
			           cv.put(Order.DATEAA, orderJO.getString(Order.DATEAA));
			           cv.put(Order.TIMEFLAG, orderJO.getString(Order.TIMEFLAG));
			           cv.put(Order.DATETIMEAE, orderJO.getString(Order.DATETIMEAE));
			           cv.put(Order.DATETIMEAA, orderJO.getString(Order.DATETIMEAA));	
			           cv.put(Order.DATETIMETL, orderJO.getString(Order.DATETIMETL));
			           cv.put(Order.DATETIMERL, orderJO.getString(Order.DATETIMERL));
			           long rowid = db.replace(table, null, cv);			      
			         	
			      //    Toast.makeText(context, "已插入一条测试数据，id =" + orderJO.getInt(Order.ID)+"  result"+ result, Toast.LENGTH_SHORT).show();
			          
		         }catch(Exception e){
		        	 e.printStackTrace();
		        	orderJA.remove(i);
		         }
		         }
		return orderJA;
	} 
	

	public boolean updateOrderState(long id, int state){
		boolean result=true;
		 System.out.println("update---------id="+id+"   state="+state);
		         try{
			           
			          //修改条件
			           
						   String whereClause= Order.ID+"=? ";
						   String [] whereArgs= new String[1];
						   whereArgs[0]=id+"";					   
						    ContentValues cv = new ContentValues();	
				            cv.put(Order.STATE, state);
				            
				    	int rowsAffected =db.update(table,cv,whereClause,whereArgs);//执行修改		    	
			          	
				    	// Toast.makeText(context, "更新一条数据，id=" +id+"  ROWS affected"+ rowsAffected, Toast.LENGTH_SHORT).show();
			
		         }catch(Exception e){
		        	 e.printStackTrace();
		        	 result=false;
		         }
		
		return result;
	} 
	
	public boolean updateBoxesIn(long id, String boxesIn){
		boolean result=true;
		 System.out.println("update---------id="+id+"   boxesIn="+boxesIn);
		         try{
			           
			          //修改条件
			           
						   String whereClause= Order.ID+"=? ";
						   String [] whereArgs= new String[1];
						   whereArgs[0]=id+"";					   
						    ContentValues cv = new ContentValues();	
				            cv.put(Order.BOXESIN, boxesIn);
				            
				    	int rowsAffected =db.update(table,cv,whereClause,whereArgs);//执行修改		    	
			          	
				    	// Toast.makeText(context, "更新一条数据，id=" +id+"  ROWS affected"+ rowsAffected, Toast.LENGTH_SHORT).show();
			
		         }catch(Exception e){
		        	 e.printStackTrace();
		        	 result=false;
		         }
		
		return result;
	} 
	
	public boolean updateOrdersState(JSONArray orderJA,int newState){
		boolean result=true;
		 System.out.println("update---------"+ orderJA);	 
		         try{			           
			          //修改条件 
                     for(int i=0;i<orderJA.size();i++){  
						   String whereClause= Order.ID+"=? ";
						   String [] whereArgs= new String[1];
						   whereArgs[0]=orderJA.getJSONObject(i).getLong(Order.ID)+"";					   
						    ContentValues cv = new ContentValues();	
				            cv.put(Order.STATE, newState);				            
				    	int rowsAffected =db.update(table,cv,whereClause,whereArgs);//执行修改		    	
			    	
                     }
		         }catch(Exception e){
		        	 e.printStackTrace();
		        	 result=false;
		         }
		
		return result;
	} 
	
	public JSONObject queryByTakeSecret(String secret){
		JSONObject record= new JSONObject();
		 System.out.println("queryByTakeSecret--------- secret="+secret);
		   try{
			   
			   String baseClause="SELECT * FROM "+table+" where ";
			   String whereClause= Order.SECRET+"=?  ";
			   String [] whereArgs= new String[1];
			   whereArgs[0]= secret;		
			  // db.execSQL("delete from Tb_Selected_Branch where Tb_Selected_Branch=?",params);
			   System.out.println("queryOneBranch  sql=      "+baseClause+whereClause);
			   Cursor c=  db.rawQuery(baseClause+whereClause, whereArgs);
			   if(c.moveToNext()){
				   
				   record.put(Order.ID, c.getLong(c.getColumnIndex(Order.ID)));          
		           record.put(Order.CUSTOMERTELNUM, c.getString(c.getColumnIndex(Order.CUSTOMERTELNUM)));
		           record.put(Order.SUPPLIERTELNUM, c.getString(c.getColumnIndex(Order.SUPPLIERTELNUM)));	
		           record.put(Order.SERVICETELNUM, c.getString(c.getColumnIndex(Order.SERVICETELNUM)));
		           record.put(Order.SECRET ,c.getString(c.getColumnIndex(Order.SECRET)));	
		           record.put(Order.BOXESIN, c.getString(c.getColumnIndex(Order.BOXESIN)));	
		           record.put(Order.STATE, c.getInt(c.getColumnIndex(Order.STATE)));	
		           record.put(Order.TIMEFLAG, c.getInt(c.getColumnIndex(Order.TIMEFLAG)));	
		           record.put(Order.DATEAA, c.getString(c.getColumnIndex(Order.DATEAA)));	
		           record.put(Order.DATETIMEAA, c.getString(c.getColumnIndex(Order.DATETIMEAA)));	 
		           record.put(Order.DATETIMEAE, c.getString(c.getColumnIndex(Order.DATETIMEAE)));
		           record.put(Order.DATETIMETL, c.getString(c.getColumnIndex(Order.DATETIMETL)));
		           record.put(Order.DATETIMERL, c.getString(c.getColumnIndex(Order.DATETIMERL))); 
				   record.put("success",true);
				  
			   }else{
				   record.put("success",false);
			   }
		   }catch( Exception e){
			  e.printStackTrace();
			  record.put("success",false);
			 
		   }finally{ 
			 // Toast.makeText(context, "查询单条记录" +record.toString(), Toast.LENGTH_SHORT).show();
		   }
		  
		   return record;
	}
	
	public JSONObject queryById(String id){
		JSONObject record= new JSONObject();
		 System.out.println("queryById--------- id="+id);
		   try{
			   
			   String baseClause="SELECT * FROM "+table+" where ";
			   String whereClause= Order.ID+"=?  ";
			   String [] whereArgs= new String[1];
			   whereArgs[0]= id;		
			  // db.execSQL("delete from Tb_Selected_Branch where Tb_Selected_Branch=?",params);
			   System.out.println("queryById  sql=      "+baseClause+whereClause);
			   Cursor c=  db.rawQuery(baseClause+whereClause, whereArgs);
			   if(c.moveToNext()){
				   
				   record.put(Order.ID, c.getLong(c.getColumnIndex(Order.ID)));          
		           record.put(Order.CUSTOMERTELNUM, c.getString(c.getColumnIndex(Order.CUSTOMERTELNUM)));
		           record.put(Order.SUPPLIERTELNUM, c.getString(c.getColumnIndex(Order.SUPPLIERTELNUM)));	
		           record.put(Order.SERVICETELNUM, c.getString(c.getColumnIndex(Order.SERVICETELNUM)));
		           record.put(Order.SECRET ,c.getString(c.getColumnIndex(Order.SECRET)));	
		           record.put(Order.BOXESIN, c.getString(c.getColumnIndex(Order.BOXESIN)));	
		           record.put(Order.STATE, c.getInt(c.getColumnIndex(Order.STATE)));	
		           record.put(Order.TIMEFLAG, c.getInt(c.getColumnIndex(Order.TIMEFLAG)));	
		           record.put(Order.DATEAA, c.getString(c.getColumnIndex(Order.DATEAA)));	
		           record.put(Order.DATETIMEAA, c.getString(c.getColumnIndex(Order.DATETIMEAA)));	 
		           record.put(Order.DATETIMEAE, c.getString(c.getColumnIndex(Order.DATETIMEAE)));
		           record.put(Order.DATETIMETL, c.getString(c.getColumnIndex(Order.DATETIMETL)));
		           record.put(Order.DATETIMERL, c.getString(c.getColumnIndex(Order.DATETIMERL)));
				  record.put("success",true);
				  
			   }else{
				 record.put("success",false);
				  
			   }
		   }catch( Exception e){
			  e.printStackTrace();
			   record.put("success",false);
			 
		   }finally{ 
			 // Toast.makeText(context, "查询单条记录" +record.toString(), Toast.LENGTH_SHORT).show();
			   System.out.println("  queryById result="+record);
		   }
		  
		   return record;
	}	
	public JSONArray  queryAll( ){
		  //Toast.makeText(context, "查询  所有", Toast.LENGTH_SHORT).show();
	 // String queryTableStr=	"select name from sqlite_master where type='table' order by name";
		JSONArray result= new JSONArray();
		      try{
		 Cursor c = db.rawQuery("SELECT * FROM "+table, null); 
	        while (c.moveToNext()) {  
	        	 JSONObject record= new JSONObject();
	        	   record.put(Order.ID, c.getLong(c.getColumnIndex(Order.ID)));          
		           record.put(Order.CUSTOMERTELNUM, c.getString(c.getColumnIndex(Order.CUSTOMERTELNUM)));
		           record.put(Order.SUPPLIERTELNUM, c.getString(c.getColumnIndex(Order.SUPPLIERTELNUM)));	
		           record.put(Order.SERVICETELNUM, c.getString(c.getColumnIndex(Order.SERVICETELNUM)));
		           record.put(Order.SECRET ,c.getString(c.getColumnIndex(Order.SECRET)));	
		           record.put(Order.BOXESIN, c.getString(c.getColumnIndex(Order.BOXESIN)));	
		           record.put(Order.STATE, c.getInt(c.getColumnIndex(Order.STATE)));	
		           record.put(Order.TIMEFLAG, c.getInt(c.getColumnIndex(Order.TIMEFLAG)));	
		           record.put(Order.DATEAA, c.getString(c.getColumnIndex(Order.DATEAA)));	
		           record.put(Order.DATETIMEAA, c.getString(c.getColumnIndex(Order.DATETIMEAA)));	 
		           record.put(Order.DATETIMEAE, c.getString(c.getColumnIndex(Order.DATETIMEAE)));
		           record.put(Order.DATETIMETL, c.getString(c.getColumnIndex(Order.DATETIMETL)));
		           record.put(Order.DATETIMERL, c.getString(c.getColumnIndex(Order.DATETIMERL)));
	        	   result.add(record);    
	        	   System.out.println("query all order sqlite      "+record);
	        }
	        c.close();  
		      }catch(Exception e){
		    	  e.printStackTrace();
		      }
		      //Toast.makeText(context, "查询产品" +result.toString(), Toast.LENGTH_SHORT).show();
		return result;		
	} 
	
	public JSONArray  queryAbandomOrder( ){
		  //Toast.makeText(context, "查询  所有", Toast.LENGTH_SHORT).show();
		JSONArray result= new JSONArray();
		      try{
		    	  String sql="SELECT * FROM "+table+" where dateTimeRL<"+Constant.CURRENT_TIME_SECOND+" and state =21 ";
		    	 
		    	  System.out.println(sql);
		 Cursor c = db.rawQuery(sql, null); 
	        while (c.moveToNext()) {  
	        	 JSONObject record= new JSONObject();
	        	 record.put(Order.ID, c.getLong(c.getColumnIndex(Order.ID)));          
		           record.put(Order.CUSTOMERTELNUM, c.getString(c.getColumnIndex(Order.CUSTOMERTELNUM)));
		           record.put(Order.SUPPLIERTELNUM, c.getString(c.getColumnIndex(Order.SUPPLIERTELNUM)));	
		           record.put(Order.SERVICETELNUM, c.getString(c.getColumnIndex(Order.SERVICETELNUM)));
		           record.put(Order.SECRET ,c.getString(c.getColumnIndex(Order.SECRET)));	
		           record.put(Order.BOXESIN, c.getString(c.getColumnIndex(Order.BOXESIN)));	
		           record.put(Order.STATE, c.getInt(c.getColumnIndex(Order.STATE)));	
		           record.put(Order.TIMEFLAG, c.getInt(c.getColumnIndex(Order.TIMEFLAG)));	
		           record.put(Order.DATEAA, c.getString(c.getColumnIndex(Order.DATEAA)));	
		           record.put(Order.DATETIMEAA, c.getString(c.getColumnIndex(Order.DATETIMEAA)));	 
		           record.put(Order.DATETIMEAE, c.getString(c.getColumnIndex(Order.DATETIMEAE)));
		           record.put(Order.DATETIMETL, c.getString(c.getColumnIndex(Order.DATETIMETL)));
		           record.put(Order.DATETIMERL, c.getString(c.getColumnIndex(Order.DATETIMERL)));
	        	   result.add(record);    
	        	   System.out.println("query abandom order sqlite      "+record);
	        }
	        c.close();  
		      }catch(Exception e){
		    	  e.printStackTrace();
		      }
		      //Toast.makeText(context, "查询产品" +result.toString(), Toast.LENGTH_SHORT).show();
		return result;		
	} 
	public JSONArray  queryTakebackOrder( ){
		  //Toast.makeText(context, "查询  所有", Toast.LENGTH_SHORT).show();
		JSONArray result= new JSONArray();
		      try{
		    	  String sql="SELECT * FROM "+table+" where dateTimeTL<"+Constant.CURRENT_TIME_SECOND+" and state =21 ";
		    	  if(Constant.courierJO.getInt(Courier.TYPE)==3) {
					  sql+=" and supplierTelnum = "+ Constant.courierJO.getString(Courier.BELONG);
				  }
		    	  System.out.println(sql);
		 Cursor c = db.rawQuery(sql, null); 
	        while (c.moveToNext()) {  
	        	 JSONObject record= new JSONObject();
	        	 record.put(Order.ID, c.getLong(c.getColumnIndex(Order.ID)));          
		           record.put(Order.CUSTOMERTELNUM, c.getString(c.getColumnIndex(Order.CUSTOMERTELNUM)));
		           record.put(Order.SUPPLIERTELNUM, c.getString(c.getColumnIndex(Order.SUPPLIERTELNUM)));	
		           record.put(Order.SERVICETELNUM, c.getString(c.getColumnIndex(Order.SERVICETELNUM)));
		           record.put(Order.SECRET ,c.getString(c.getColumnIndex(Order.SECRET)));	
		           record.put(Order.BOXESIN, c.getString(c.getColumnIndex(Order.BOXESIN)));	
		           record.put(Order.STATE, c.getInt(c.getColumnIndex(Order.STATE)));	
		           record.put(Order.TIMEFLAG, c.getInt(c.getColumnIndex(Order.TIMEFLAG)));	
		           record.put(Order.DATEAA, c.getString(c.getColumnIndex(Order.DATEAA)));	
		           record.put(Order.DATETIMEAA, c.getString(c.getColumnIndex(Order.DATETIMEAA)));	 
		           record.put(Order.DATETIMEAE, c.getString(c.getColumnIndex(Order.DATETIMEAE)));
		           record.put(Order.DATETIMETL, c.getString(c.getColumnIndex(Order.DATETIMETL)));
		           record.put(Order.DATETIMERL, c.getString(c.getColumnIndex(Order.DATETIMERL)));
	        	   result.add(record);    
	        	   System.out.println("query takeback order       "+record);
	        }
	        c.close();  
		      }catch(Exception e){
		    	  e.printStackTrace();
		      }
		      //Toast.makeText(context, "查询产品" +result.toString(), Toast.LENGTH_SHORT).show();
		return result;		
	} 
	public JSONArray  queryToArriveOrder( ){
		  //Toast.makeText(context, "查询  所有", Toast.LENGTH_SHORT).show();
		JSONArray result= new JSONArray();
		  try{
			  String dateAA= Constant.CURRENT_TIME_SECOND+"";
			  dateAA=dateAA.substring(0,8);
			  String sql="SELECT * FROM "+table+" where dateAA="+dateAA+" and timeFlag="+Constant.current_timeFlag+" and state <21 ";
			  if(Constant.courierJO.getInt(Courier.TYPE)==3) {
				  sql+=" and supplierTelnum = "+ Constant.courierJO.getString(Courier.BELONG);
			  }
			  System.out.println(sql);
			  Cursor c = db.rawQuery(sql, null); 
	        while (c.moveToNext()) {  
	        	 JSONObject record= new JSONObject();
	        	 record.put(Order.ID, c.getLong(c.getColumnIndex(Order.ID)));          
		           record.put(Order.CUSTOMERTELNUM, c.getString(c.getColumnIndex(Order.CUSTOMERTELNUM)));
		           record.put(Order.SUPPLIERTELNUM, c.getString(c.getColumnIndex(Order.SUPPLIERTELNUM)));	
		           record.put(Order.SERVICETELNUM, c.getString(c.getColumnIndex(Order.SERVICETELNUM)));
		           record.put(Order.SECRET ,c.getString(c.getColumnIndex(Order.SECRET)));	
		           record.put(Order.BOXESIN, c.getString(c.getColumnIndex(Order.BOXESIN)));	
		           record.put(Order.STATE, c.getInt(c.getColumnIndex(Order.STATE)));	
		           record.put(Order.TIMEFLAG, c.getInt(c.getColumnIndex(Order.TIMEFLAG)));	
		           record.put(Order.DATEAA, c.getString(c.getColumnIndex(Order.DATEAA)));	
		           record.put(Order.DATETIMEAA, c.getString(c.getColumnIndex(Order.DATETIMEAA)));	 
		           record.put(Order.DATETIMEAE, c.getString(c.getColumnIndex(Order.DATETIMEAE)));
		           record.put(Order.DATETIMETL, c.getString(c.getColumnIndex(Order.DATETIMETL)));
		           record.put(Order.DATETIMERL, c.getString(c.getColumnIndex(Order.DATETIMERL)));
	        	   result.add(record);    
	        	   System.out.println("query toarrive order -- "+record);
	        }
	        c.close();  
		      }catch(Exception e){
		    	  e.printStackTrace();
		      }
		      //Toast.makeText(context, "查询产品" +result.toString(), Toast.LENGTH_SHORT).show();
		return result;		
	} 
	
	public JSONArray  queryTakedOrder( ){
		  
		JSONArray result= new JSONArray();
		  try{
			 
			  String sql="SELECT * FROM "+table+" where  state =31 ";		  
			  System.out.println(sql);
			  Cursor c = db.rawQuery(sql, null); 
	        while (c.moveToNext()) {  
	        	 JSONObject record= new JSONObject();
	        	 record.put(Order.ID, c.getLong(c.getColumnIndex(Order.ID)));          
		           record.put(Order.CUSTOMERTELNUM, c.getString(c.getColumnIndex(Order.CUSTOMERTELNUM)));
		           record.put(Order.SUPPLIERTELNUM, c.getString(c.getColumnIndex(Order.SUPPLIERTELNUM)));	
		           record.put(Order.SERVICETELNUM, c.getString(c.getColumnIndex(Order.SERVICETELNUM)));
		           record.put(Order.SECRET ,c.getString(c.getColumnIndex(Order.SECRET)));	
		           record.put(Order.BOXESIN, c.getString(c.getColumnIndex(Order.BOXESIN)));	
		           record.put(Order.STATE, c.getInt(c.getColumnIndex(Order.STATE)));	
		           record.put(Order.TIMEFLAG, c.getInt(c.getColumnIndex(Order.TIMEFLAG)));	
		           record.put(Order.DATEAA, c.getString(c.getColumnIndex(Order.DATEAA)));	
		           record.put(Order.DATETIMEAA, c.getString(c.getColumnIndex(Order.DATETIMEAA)));	 
		           record.put(Order.DATETIMEAE, c.getString(c.getColumnIndex(Order.DATETIMEAE)));
		           record.put(Order.DATETIMETL, c.getString(c.getColumnIndex(Order.DATETIMETL)));
		           record.put(Order.DATETIMERL, c.getString(c.getColumnIndex(Order.DATETIMERL)));
	        	   result.add(record);    
	        	   System.out.println("query toarrive order -- "+record);
	        }
	        c.close();  
		      }catch(Exception e){
		    	  e.printStackTrace();
		      }
		      //Toast.makeText(context, "查询产品" +result.toString(), Toast.LENGTH_SHORT).show();
		return result;		
	} 
	
	public boolean deleteOldOrder(){
		 boolean result=false; int rowAffected=0;
		 System.out.println("--------deleteOldOrder---------");
		   try{
			   
			   String whereClause= Order.DATETIMEAA+"< ? ";
			   String [] whereArgs= new String[5];
			   Date timeOnLine= DateTimeFunction.parseMyDateFormat(Constant.CURRENT_TIME_SECOND+"");
			   Date  datesAgo=DateTimeFunction.addHour( timeOnLine, 0-Constant.OLD_ORDER_DELETE_Hour);			   
			   whereArgs[0]= DateTimeFunction.myDateFormat(datesAgo);
			  
			  // db.execSQL("delete from Tb_Selected_Branch where Tb_Selected_Branch=?",params);
			   rowAffected=  db.delete(table, whereClause, whereArgs);
			   result=true;
		   }catch( Exception e){
			  e.printStackTrace();
			  result=false;
		   }finally{ 
			 //  Toast.makeText(context, "删除过时订单" +"  rowAffected="+rowAffected+"条记录", Toast.LENGTH_SHORT).show();
		   }
		  
		   return result;
	}
	

	public boolean deleteAll(){
		boolean result=false;
		   try{
			  // db.execSQL("delete from Tb_Selected_Branch where Tb_Selected_Branch=?",params);
			   db.delete("Tb_Selected_Branch", null, null);
			   result=true;
		   }catch( Exception e){
			  e.printStackTrace();
			  result=false;
		   }finally{ 
			   //Toast.makeText(context, "删除一个店所有购物车内东西" +branch.toString(), Toast.LENGTH_SHORT).show();
		   }
		  
		   return result;
	}
	

	@Override
	protected void finalize() throws Throwable {
		// TODO Auto-generated method stub
		super.finalize();
		if(db.isOpen()) {db.close();}
	}

	}
